# -*- coding: utf-8 -*-
"""
Created on Fri Jan 15 22:06:36 2021

@author: 10365
"""


'''
    table structure information
'''
class TableInfo():
    def __init__(self,name,dbType):
        self.tableName = name
        self.datebaseType = dbType 
        self.columnName = []
        self.columnType = []
        self.rowNum = 0
        self.primaryKey = []
        self.tableData = None
        
    def setRowNum(self,rowNum):
        try:
            self.rowNum = int(rowNum)
        except Exception as err:
            print(rowNum)
            raise err
        return
    
    def setColumnName(self,colName):
        if not type(colName) == list:
            raise Exception("colName must be a list")
        self.columnName = colName
        return

    def setTableData(self,data):
        if not type(data) == list:
            raise Exception("table data must be list")
        self.tableData = data
        return
    
    def setPrimaryKey(self,keys):
        if not type(keys) == list:
            raise Exception("keys must be a list")
        self.primaryKey = keys
        return
    
    def setColumnType(self,colType):
        if not type(colType) == list:
            raise Exception("keys must be a list")
        self.columnType = colType
        return
    


def typeParse(value,colType):
    '''
        analysis the column type
    '''
    if colType == "VARCHAR" or colType == "CHAR":
        value = '\'' + value + '\''
    return value




def dealValueByType(tableInfo:TableInfo):
    '''
        deal value by the column type
    '''
    for row_i in range(0,tableInfo.rowNum):
        row = tableInfo.tableData[row_i]
        for col_i in range(0,len(tableInfo.columnType)):
            colType = tableInfo.columnType[col_i]
            value = row[col_i]
            row[col_i] = typeParse(value,colType)
        tableInfo.tableData[row_i] = row
    return


def genInsertSql(tableInfo:TableInfo): 
    '''
        generate insert sql 
    '''
    #处理数据值的类型
    dealValueByType(tableInfo)
    #拼装SQL语句
    tableName = tableInfo.tableName
    columnNamesStr = ','.join(tableInfo.columnName)
    if tableInfo.datebaseType == 'MYSQL':
        valueStrList = []
        for row in tableInfo.tableData:
            valueStrList.append('(' + ','.join(row) + ')')
        valuesStr = ',\n'.join(valueStrList)
        sql = f'insert into {tableName}({columnNamesStr}) values \n{valuesStr};'
    elif tableInfo.datebaseType == 'ORACLE':
        pass
    else:
        raise Exception("no such database")
    print(sql)
    return sql
        

def saveSql(fileName,sql):
    '''
        保存SQL
    '''
    with open(fileName,"w+",encoding="utf-8") as fp:
        fp.write(sql)
    print("SQL is saved in " + fileName + "and encoding in UTF-8")
    
    
        